import pymysql
from sqlalchemy import create_engine
import pandas as pd


def init_db():
	create_sql = '''
	CREATE DATABASE IF NOT EXISTS house_lianjia;
	USE house_lianjia;
	CREATE TABLE IF NOT EXISTS `t_house`(
		`title`       varchar(128) COLLATE utf8mb4_general_ci DEFAULT NULL,
		`name`        varchar(16) COLLATE utf8mb4_general_ci  DEFAULT NULL,
		`area`        varchar(16) COLLATE utf8mb4_general_ci  DEFAULT NULL COMMENT '区域',
		`modal`       varchar(16) COLLATE utf8mb4_general_ci  DEFAULT NULL COMMENT '房型',
		`extent`      varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '面积',
		`direct`      varchar(4) COLLATE utf8mb4_general_ci   DEFAULT NULL COMMENT '方向',
		`decoration`  varchar(16) COLLATE utf8mb4_general_ci  DEFAULT NULL COMMENT '装修',
		`floor`       varchar(16) COLLATE utf8mb4_general_ci  DEFAULT NULL COMMENT '楼层',
		`structure`   varchar(16) COLLATE utf8mb4_general_ci  DEFAULT NULL COMMENT '结构',
		`like`        int                                     DEFAULT NULL COMMENT '关注',
		`time`        varchar(16) COLLATE utf8mb4_general_ci  DEFAULT NULL COMMENT '时间',
		`total_price` decimal(10, 2)                          DEFAULT NULL COMMENT '总价',
		`price`       decimal(10, 2)                          DEFAULT NULL COMMENT '单价',
		`tag`         varchar(32) COLLATE utf8mb4_general_ci  DEFAULT NULL COMMENT '标签'
	) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci;
	'''


def save_model(data):
	host = 'localhost'
	user = 'root'
	password = '123456'
	db = 'house_lianjia'
	port = 3306
	# 插入数据库中的表，若不存在则新建
	table = 't_house'
	engine = create_engine(
		'mysql+pymysql://{}:{}@{}:{}/{}'.format(user, password, host, port, db))
	data.to_sql(table, engine, chunksize=10000, if_exists="append", index=False)
	print('保存到数据库成功！')
